﻿using DbTestDataGenerate.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DbTestDataGenerate
{
    public static class MysqlCommad
    {
        /// <summary>
        /// 数据库名
        /// </summary>
        public static string DbName = "dbtestgrand";

        /// <summary>
        /// 获取数据库中的表
        /// </summary>
        /// <returns></returns>
        public static List<string> GetDataBaseTableList()
        {
            List<string> result = new List<string>();

            string sql = string.Format("select table_name from information_schema.tables where table_schema='{0}' and table_type='base table'", DbName);
            DataTable dt = MysqlHelper.GetDataTable(MysqlHelper.Conn, System.Data.CommandType.Text, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                result.Add(dt.Rows[i][0].ToString());
            }

            return result;
        }

        /// <summary>
        /// 获取表中的字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static List<ColumnDto> GetTableColumn(string tableName)
        {
            List<ColumnDto> result = new List<ColumnDto>();
            string sql = string.Format("select column_name,data_type,column_key,Extra,character_maximum_length from information_schema.COLUMNS where  table_schema='{0}' and table_name = '{1}';  ", DbName, tableName);
            DataTable dt = MysqlHelper.GetDataTable(MysqlHelper.Conn, System.Data.CommandType.Text, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //result.Add(dt.Rows[i][0].ToString());
                ColumnDto dto = new ColumnDto();
                dto.ColumnName = dt.Rows[i]["column_name"].ToString();
                dto.ColumnType = dt.Rows[i]["data_type"].ToString();
                dto.Column_key = dt.Rows[i]["column_key"].ToString();
                dto.Extra = dt.Rows[i]["Extra"].ToString();
                dto.Character_maximum_length = dt.Rows[i]["character_maximum_length"].ToString();
                result.Add(dto);
            }
            return result;
        }

        /// <summary>
        /// 获取表中的主键外键
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static List<ReferencedColumnDto> GetTableConstraint(string tableName)
        {
            List<ReferencedColumnDto> result = new List<ReferencedColumnDto>();
            string sql = string.Format("SELECT constraint_name,column_name,referenced_table_name FROM information_schema.key_column_usage WHERE table_name='{0}' and table_schema='{1}'", tableName, DbName);
            DataTable dt = MysqlHelper.GetDataTable(MysqlHelper.Conn, System.Data.CommandType.Text, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //result.Add(dt.Rows[i][0].ToString());
                ReferencedColumnDto dto = new ReferencedColumnDto();
                dto.Constraint_name = dt.Rows[i]["constraint_name"].ToString();
                dto.ColumnName = dt.Rows[i]["column_name"].ToString();
                dto.Referenced_table_name = dt.Rows[i]["referenced_table_name"].ToString();
                result.Add(dto);
            }
            return result;
        }

        /// <summary>
        /// 获取生成的sql insert into test1(Valuestr) values("xxx"),("xxx1")
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static List<string> GetInsertSql(string tableName)
        {
            List<string> result = new List<string>();

            List<ColumnDto> listColumn = GetTableColumn(tableName);//所有列的信息
            List<ReferencedColumnDto> listReferenced = GetTableConstraint(tableName);//主键，外键信息

            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.AppendFormat("insert into {0} (", tableName);

            List<ColumnDto> listInsertColumn = new List<ColumnDto>();//插入字段
            foreach (var item in listColumn)
            {
                var refernced = listReferenced.Where(x => x.ColumnName == item.ColumnName).FirstOrDefault();
                if (refernced!=null)
                {
                    if (refernced.IsPRIMARY)
                    {//主键
                        continue;
                    }
                    if (refernced.IsPK)
                    {//外键
                        sqlBuilder.AppendFormat("`{0}`,", item.ColumnName);
                        item.IsForeignKey = true;
                        listInsertColumn.Add(item);
                        continue;
                    }
                }
                else
                {
                    //`ContextKey`
                    sqlBuilder.AppendFormat("`{0}`,", item.ColumnName);
                    listInsertColumn.Add(item);
                }
            }

            string sql = sqlBuilder.ToString().Substring(0, sqlBuilder.Length - 1)+")";

            StringBuilder sqlValue = new StringBuilder();
            sqlValue.Append(" values ");

            int count = 0;
            int maxRow = 500;
            while (count< maxRow)
            {
                for (int i = 0; i < listInsertColumn.Count; i++)
                {
                    var columnDto = listInsertColumn[i];
                    if (i == 0)
                    {
                        sqlValue.Append("(");
                    }

                    if (columnDto.IsForeignKey)
                    {
                        sqlValue.AppendFormat((count + 1).ToString());
                    }
                    else
                    {
                        string randValue = GetRandomValue(columnDto.ColumnType, columnDto.Character_maximum_length.TryInt(2));
                        sqlValue.AppendFormat("{0}", randValue);
                    }
                 


                    if (i == listInsertColumn.Count - 1)
                    {
                        sqlValue.Append(")");
                    }
                    else
                    {
                        sqlValue.Append(",");
                    }
                }
                if (count + 1 != maxRow)
                {
                    sqlValue.Append(",");
                }
        

                count ++;
            }
            string insertSql = sql + sqlValue.ToString();
            return result;
        }

        /// <summary>
        /// 根据不同的数据类型生成测试数据
        /// 
        /// </summary>
        /// <param name="dataType">数据类型</param>
        /// <param name="lenght">字段长度</param>
        /// <returns></returns>
        public static string GetRandomValue(string dataType,int lenght)
        {
            //bigint
            //binary
            //bit
            //blob
            //bool
            //boolean
            //char
            //date
            //datetime
            //decimal
            //double
            //enum
            //float
            //int
            //longblob
            //longtext
            //mediumblob
            //mediumint
            //mediumtext
            //numeric
            //real
            //set
            //smallint
            //text
            //time
            //timestamp
            //tinyblob
            //tinyint
            //tinytext
            //varbinary
            //varchar
            //year

            string result = string.Empty;
            dataType = dataType.ToLower();
            switch (dataType)
            {
                case "tinyint"://
                case "bit":
                    result ="0";
                    break;
                case "smallint"://
                case "mediumint"://
                case "int"://
                case "bigint"://
                    result = RandomExtensions.GetRandomNum(2);
                    break;
                case "float"://单精度浮点数
                case "double"://双精度浮点数
                case "decimal"://压缩的“严格”定点数
                    result = RandomExtensions.GetRandomNum(2);
                    break;
                case "tinytext"://
                case "text"://
                case "varchar":
                    result ="'"+ RandomExtensions.GetRandChinese(5) + "'";
                    break;
                case "mediumtext"://
                case "longtext"://
                    result ="'" + RandomExtensions.GetRandChinese(10) + "'";
                    break;
                case "datetime"://
                    result ="'"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                    break;
                case "date"://
                case "timestamp"://
                case "time"://
                case "year"://  
                    break;
            }
            return result;
        }
    }
}
